package com.fc.v2.controller;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.fc.v2.service.StatisticsSpatialService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.fc.v2.service.SysUtilService;

@Controller
@RequestMapping("statistics")
public class StatisticsSpatialController {
	private static Logger logger = LoggerFactory.getLogger(StatisticsSpatialController.class);
	@Autowired
	private SysUtilService sysUtilService;
	@Autowired
	private StatisticsSpatialService statisticsSpatialService;

	/*
	 * 按管径统计
	 * */
	///次功能需要现在desktop中创建长度字段，并计算出每个管线的长度
	/*
	* 返回结果格式：
	* {
		0: [{管径: 400}, {管径: 500}, {管径: 600}, {管径: 800}, {管径: 1000}, {管径: 1200}]
		1: [{长度: 0, 管径: 400}, {长度: 0, 管径: 500}, {长度: 0, 管径: 600}, {长度: 0, 管径: 800},…]
		2: [{长度: 3889.602450080578, 管径: 400}, {长度: 1859.6421889847875, 管径: 500}, {长度: 1014.223227231081, 管径: 600},…
		}
	* */
	@GetMapping("spatialstatistics")
	@ResponseBody
	public ArrayList<List<Map<Object,Object>>> statisticsByGJ(String tbNames){
		logger.debug("StatisticsByGJ,TableName");
		
		if(tbNames == null) return null;
		String[] tbNamesArr = tbNames.split(","); 
		if(tbNamesArr.length == 0) return null;

		String sqlGJ = "SELECT DISTINCT 管径  " + 
				"FROM (\r\n" + 
				"	SELECT DISTINCT 管径 " + 
				"	FROM "+ tbNamesArr[0] +" " ;
		for (int i = 1; i < tbNamesArr.length; i++) {
			if(tbNamesArr[i] == null || tbNamesArr[i].length() == 0)
				continue;
			sqlGJ+= "	UNION ALL\r\n" +
					"	SELECT DISTINCT 管径 " + 
					"	FROM "+tbNamesArr[i]+" " ;
		}
		sqlGJ += ") T ";
		
		sqlGJ = "SELECT T.* FROM ( " + sqlGJ + " ) T  ORDER BY 管径";

		ArrayList<List<Map<Object,Object>>> result = new ArrayList<List<Map<Object,Object>>>();
		List<Map<Object,Object>> gjs = sysUtilService.SelectExecuteSQL(sqlGJ);
		result.add(gjs);
		for (int i = 0; i < tbNamesArr.length; i++) {
			if(tbNamesArr[i] == null || tbNamesArr[i].length() == 0)
				continue;
			String sqlTmp ="SELECT 管径, SUM(长度) AS 长度  FROM " + tbNamesArr[i] + " GROUP BY 管径  ORDER BY 管径";
			List<Map<Object,Object>> resultInOneLines = sysUtilService.SelectExecuteSQL(sqlTmp);
			if(resultInOneLines.size() == 0) continue;
			List<Map<Object,Object>> resultInOneLinesTmp = new ArrayList<Map<Object,Object>>();
			for (int j = 0; j <gjs.size() ; j++) {
				Object theGJ = gjs.get(j).get("管径");
				Map<Object,Object> contained = statisticsSpatialService.containsValue(resultInOneLines,theGJ);
				if(contained == null){
					Map<Object,Object> newVal = new HashMap<>();
					newVal.put("管径",theGJ);
					newVal.put("长度",0);
					resultInOneLinesTmp.add(newVal);
				}
				else{
					resultInOneLinesTmp.add(contained);
				}
			}
			result.add(resultInOneLinesTmp);
		}
		
		return result;
	}

	@GetMapping("spatialstatistics/cz")
	@ResponseBody
	public ArrayList<List<Map<Object,Object>>> statisticsByCz(String tbNames){
		if(tbNames == null) return null;
		String[] tbNamesArr = tbNames.split(",");
		if(tbNamesArr.length == 0) return null;

		if(tbNames == null) return null;

		String sqlGJ = "SELECT DISTINCT 材质  " +
				"FROM ( " +
				"	SELECT DISTINCT 材质 " +
				"	FROM "+ tbNamesArr[0] +" " ;
		for (int i = 1; i < tbNamesArr.length; i++) {
			if(tbNamesArr[i] == null || tbNamesArr[i].length() == 0)
				continue;
			sqlGJ+= "	UNION ALL " +
					"	SELECT DISTINCT 材质 " +
					"	FROM "+tbNamesArr[i]+" " ;
		}
		sqlGJ += ") T ";

		sqlGJ = "SELECT IFNULL(材质,\"未知\") as 材质 FROM ( " + sqlGJ + " ) T  ORDER BY 材质";

		ArrayList<List<Map<Object,Object>>> result = new ArrayList<List<Map<Object,Object>>>();
		List<Map<Object,Object>> gjs = sysUtilService.SelectExecuteSQL(sqlGJ);
		result.add(gjs);
		for (int i = 0; i < tbNamesArr.length; i++) {
			if(tbNamesArr[i] == null || tbNamesArr[i].length() == 0)
				continue;
			String sqlTmp ="SELECT IFNULL(材质,\"未知\" ) as 材质, SUM(长度) AS 长度  FROM " + tbNamesArr[i] + " GROUP BY 材质  ORDER BY 材质";
			List<Map<Object,Object>> resultInOneLines = sysUtilService.SelectExecuteSQL(sqlTmp);
			if(resultInOneLines.size() == 0) continue;
			List<Map<Object,Object>> resultInOneLinesTmp = new ArrayList<Map<Object,Object>>();
			for (int j = 0; j <gjs.size() ; j++) {
				Object theGJ = gjs.get(j).get("材质");
				Map<Object,Object> contained = statisticsSpatialService.containsValue(resultInOneLines,theGJ);
				if(contained == null){
					Map<Object,Object> newVal = new HashMap<>();
					newVal.put("材质",theGJ);
					newVal.put("长度",0);
					resultInOneLinesTmp.add(newVal);
				}
				else{
					resultInOneLinesTmp.add(contained);
				}
			}
			result.add(resultInOneLinesTmp);
		}

		return result;
	}
	/*
	* 按管径分段统计
	* */
	@GetMapping("spatialstatistics/segs/gj")
	@ResponseBody
	public ArrayList<List<Map<Object,Object>>> statisticsBySegsGJ(String tbNames,int segCount){
		if(tbNames == null) return null;
		String[] tbNamesArr = tbNames.split(",");
		if(tbNamesArr.length == 0) return null;

		String sqlMinMax = "select MINMAX(管径) as 管径 from \n" +
				"(\n" +
				" select MINMAX(管径) as 管径 from " + tbNamesArr[0] ;
		for (int i = 1; i < tbNamesArr.length; i++) {
			if (tbNamesArr[i] == null || tbNamesArr[i].length() == 0)
				continue;
			sqlMinMax += " union all \n" +
					" select MINMAX(管径) as 管径 from  " + tbNamesArr[i];
		}
		sqlMinMax += ") as T";

		String sqlMin = sqlMinMax.replace("MINMAX","min");
		String sqlMax = sqlMinMax.replace("MINMAX","max");
		List<Map<Object,Object>> minTmp = sysUtilService.SelectExecuteSQL(sqlMin);
		List<Map<Object,Object>> maxTmp = sysUtilService.SelectExecuteSQL(sqlMax);

		double minR = 0,maxR = 0;
		if(minTmp.size() > 0){
			Object gjTmp = minTmp.get(0).get("管径");
			if(gjTmp != null) {
				minR = Double.parseDouble(gjTmp.toString());
			}
		}
		if(maxTmp.size() > 0){
			Object gjTmp = maxTmp.get(0).get("管径");
			if(gjTmp != null) {
				maxR = Double.parseDouble(gjTmp.toString());
			}
		}
		if(maxR == minR) {
			maxR += 10;
			minR -= 10;
		};
		maxR = maxR + 0.1;
		minR = minR - 0.1;
		if(minR < 0) minR = 0;
		double segSetp = (maxR - minR)/(segCount-1);
		//minR = minR - segSetp/2;
		//maxR = maxR + segSetp/2;
		ArrayList<List<Map<Object,Object>>> result = new ArrayList<>();
		List<Map<Object,Object>> titles = new ArrayList<>();
		for (double j = minR; j <= maxR; j+=segSetp ) {
			String tmpMin = String .format("%.2f",j);
			String tmpMax = String .format("%.2f",j + segSetp);
			String segTitle = tmpMin + " ≤ X < " + tmpMax;
			Map<Object, Object> title = new HashMap<>();
			title.put("管径", segTitle);
			titles.add(title);
		}
		result.add(titles);
		for (int i = 0; i < tbNamesArr.length; i++) {
			if (tbNamesArr[i] == null || tbNamesArr[i].length() == 0)
				continue;
			String sqlOrg = "SELECT * FROM (";
			String sql01 = "";
			for (double j = minR; j <= maxR; j+=segSetp ) {
				String tmpMin = String .format("%.2f",j);
				String tmpMax = String .format("%.2f",j + segSetp);
				String segTitle ="\""+ tmpMin + " ≤ X < " + tmpMax + "\"";
				if(j > minR) sql01 += " UNION ALL ";
				sql01 += " SELECT "+segTitle+" AS 管径,SUM(长度) AS 长度 FROM "+tbNamesArr[i]+" WHERE 管径 >= "+j+" AND 管径 < " + (j+segSetp);
			}
			sqlOrg += sql01;
			sqlOrg += ") T";
			result.add(sysUtilService.SelectExecuteSQL(sqlOrg));
		}
		return result;
	}
	@GetMapping("spatialstatistics/segs/ms")
	@ResponseBody
	public ArrayList<List<Map<Object,Object>>> statisticsBySegsMS(String tbNames,int segCount){
		if(tbNames == null) return null;
		String[] tbNamesArr = tbNames.split(",");
		if(tbNamesArr.length == 0) return null;

		String sqlMinMax = "select MINMAX(埋深) as 埋深 from \n" +
				"(\n" +
				" select MINMAX(SSStart_Depth) as 埋深 from " + tbNamesArr[0] ;
		for (int i = 1; i < tbNamesArr.length; i++) {
			if (tbNamesArr[i] == null || tbNamesArr[i].length() == 0)
				continue;
			sqlMinMax += " union all \n" +
					" select MINMAX(SSStart_Depth) as 埋深 from  " + tbNamesArr[i];
		}
		sqlMinMax += ") as T";

		String sqlMin = sqlMinMax.replace("MINMAX","min");
		String sqlMax = sqlMinMax.replace("MINMAX","max");
		List<Map<Object,Object>> minTmp = sysUtilService.SelectExecuteSQL(sqlMin);
		List<Map<Object,Object>> maxTmp = sysUtilService.SelectExecuteSQL(sqlMax);

		double minR = 0,maxR = 0;
		if(minTmp.size() > 0){
			Object gjTmp = minTmp.get(0).get("埋深");
			if(gjTmp != null) {
				minR = Double.parseDouble(gjTmp.toString());
			}
		}
		if(maxTmp.size() > 0){
			Object gjTmp = maxTmp.get(0).get("埋深");
			if(gjTmp != null) {
				maxR = Double.parseDouble(gjTmp.toString());
			}
		}
		if(maxR == minR) {
			maxR += 10;
			minR -= 10;
		};
		maxR = maxR + 0.1;
		minR = minR - 0.1;
		if(minR < 0) minR = 0;
		double segSetp = (maxR - minR)/(segCount-1);
		//minR = minR - segSetp/2;
		//maxR = maxR + segSetp/2;
		ArrayList<List<Map<Object,Object>>> result = new ArrayList<>();
		List<Map<Object,Object>> titles = new ArrayList<>();
		for (double j = minR; j <= maxR; j+=segSetp ) {
			String tmpMin = String .format("%.2f",j);
			String tmpMax = String .format("%.2f",j + segSetp);

			String segTitle = tmpMin + " ≤ X < " + tmpMax;
			if(j+segSetp >= maxR){
				segTitle = tmpMin + " ≤ X ≤ " + tmpMax;
			}
			Map<Object, Object> title = new HashMap<>();
			title.put("埋深", segTitle);
			titles.add(title);
		}
		result.add(titles);
		for (int i = 0; i < tbNamesArr.length; i++) {
			if (tbNamesArr[i] == null || tbNamesArr[i].length() == 0)
				continue;
			String sqlOrg = "SELECT * FROM (";
			String sql01 = "";
			for (double j = minR; j <= maxR; j+=segSetp ) {
				String tmpMin = String .format("%.2f",j);
				String tmpMax = String .format("%.2f",j + segSetp);

				String segTitle ="\""+ tmpMin + " ≤ X < " + tmpMax + "\"";
				if(j+segSetp >= maxR){
					segTitle = "\"" + tmpMin + " ≤ X ≤ " + tmpMax + "\"";
				}
				if(j > minR) sql01 += " UNION ALL ";
				sql01 += " SELECT "+segTitle+" AS 埋深,SUM(长度) AS 长度 FROM "+tbNamesArr[i]+" WHERE SSStart_Depth >= "+j+" AND SSStart_Depth < " + (j+segSetp);
			}
			sqlOrg += sql01;
			sqlOrg += ") T";
			result.add(sysUtilService.SelectExecuteSQL(sqlOrg));
		}
		return result;
	}
}
